package com.fahai.utils;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.springframework.beans.BeansException;
import org.springframework.core.io.support.PropertiesLoaderUtils;

import javax.management.RuntimeErrorException;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


public class ExcelExport {
	private static Logger logger = Logger.getLogger(ExcelExport.class);
	/*properties*/
	private Properties properties;
	public ExcelExport(){}
	public ExcelExport(String propFilePath){
		try {
			properties = PropertiesLoaderUtils.loadAllProperties(propFilePath);
		} catch (IOException e) {
			logger.error(e);
			throw new RuntimeErrorException(new Error(e.getMessage()));
		}
	}	

	/*取配置文件excelToBean.properties的值**/
	public Object getVale(String key) {
		return properties.getProperty(key);
	}
	
	/***
	 * 从配置文件取出信息--》组装Map
	 * map{"classFullName"："com....",sheetIndex:"1","startRow":"1"
	 * 		beanMap:map{"col1":"attr1","col2":"attr2".....}
	 * }
	 * @param propertBeanName  javabean名称，与配置文件excelToBean.properties中的内容对应
	 * @return
	 */
	public Map<String, Object> xmlToBeanMap(String propertBeanName) {
		SAXReader reader = new SAXReader();
		String path = this.properties.getProperty(propertBeanName);
		Map<String, Object> rootMap = new HashMap<String, Object>();
		try {
			Document doc = reader.read(ExcelExport.class.getClassLoader().getResourceAsStream(path));
			Element root = doc.getRootElement();
			Element el = root.element("class");
			String classFullName = el.attribute("name").getText();
			rootMap.put("classFullName", classFullName);
			String sheetIndex = el.attribute("sheetIndex").getText();
			rootMap.put("sheetIndex", sheetIndex);
			String startRow = el.attribute("startRow").getText();
			rootMap.put("startRow", startRow);
			Map<String, String> beanMap = new HashMap<String, String>();
			Element child = null;
			for(Iterator<?> iter = el.elementIterator(); iter.hasNext();){
				child = (Element) iter.next();
				beanMap.put(child.attribute("col").getText(), child.attribute("name").getText());
			}
			rootMap.put("beanMap", beanMap);
		} catch (DocumentException e) {
			logger.error(e);
			e.printStackTrace();
		}
		return rootMap;
	}
	
	/* (non-Javadoc)
	 * @see javacommon.excelUtils.FileParser#excelToList(java.io.InputStream, java.lang.String)
	 */
	public List excelToList(InputStream inputStream,String propertBeanName) {
		Map map = xmlToBeanMap(propertBeanName); //取出已组装的map
		List beanList = new ArrayList();
		try {
			POIFSFileSystem fs = new POIFSFileSystem(inputStream);
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			String sheetIndex = (String) map.get("sheetIndex");  //取出配置文件中设置的sheetIndex页
			if(sheetIndex == null || "".equals(sheetIndex.trim())) {
				return null;
			}
			HSSFSheet sheet = wb.getSheetAt(Integer.valueOf(sheetIndex.trim()).intValue()-1);
			int lastRow = sheet.getLastRowNum();
			HSSFRow row = null;
			HSSFCell cell = null;
			int statrRow = map.get("startRow") == null ? 0 : (Integer.valueOf(((String)map.get("startRow")).trim()).intValue()-1);
			Map<String,Object> beanMap = (Map<String, Object>) map.get("beanMap"); //取出 beanMap
			Set<String> set = beanMap.keySet();
			String key = null;
			Object val = null;
			//bean全类名
			String classFullName = (String) map.get("classFullName");
			Class clazz = Class.forName(classFullName);
			Method[] methods = clazz.getMethods();
			for(int i=statrRow; i<=lastRow; i++) {
				row = sheet.getRow(i);
				Object obj = clazz.newInstance();
				for(Iterator<String> iter = set.iterator();iter.hasNext();) {
					key = iter.next();//取出配置文件中对应列值
					val = beanMap.get(key);//取出配置文件中对应bean属性名
					for (int j = 0; j < methods.length; j++) {
						Method method = methods[j];
						String methodName = method.getName();
						if(methodName.startsWith("set") && methodName.equals("set"+upperCaseFirst((String)val))) {
							Class[] classes = method.getParameterTypes();
							String parmTypeName = classes[0].getName();
							String cellValue = getCellFormatValue(row.getCell(Integer.valueOf(key.trim().trim()).intValue()-1)); //取得cell值
							//根据不同类型设置值
							if(parmTypeName.equals("java.lang.String")) {
								method.invoke(obj, cellValue);
							} else if(parmTypeName.equals("java.lang.Double")) {
								method.invoke(obj,"".equals(cellValue.trim()) ? null : Double.valueOf(cellValue));
							} else if(parmTypeName.equals("java.lang.Integer")) {
								method.invoke(obj,"".equals(cellValue.trim()) ? null : Integer.valueOf(Double.valueOf(cellValue).intValue()));
							} else if(parmTypeName.equals("java.util.Date")) {
								Date date = null;
								if(!"".equals(cellValue.trim())) {
									SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
									date = sdf.parse(cellValue.trim());
								} 
								method.invoke(obj,date);
							} else if(parmTypeName.equals("java.lang.Float")) {
								method.invoke(obj,"".equals(cellValue.trim()) ? null : Float.valueOf(cellValue));
							}
						}
					}
				}
				beanList.add(obj);
			}
		} catch (FileNotFoundException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (IOException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (InstantiationException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (BeansException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (NumberFormatException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (ParseException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			if(inputStream != null ) {
				try {
					inputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}  
		return beanList;
	}

	/* (non-Javadoc)
	 * @see javacommon.excelUtils.FileParser#expExcel(java.lang.String, java.lang.String[], java.util.List, java.io.OutputStream)
	 */
	public static void expExcel(String title,String[] headers,List dataset, OutputStream os) throws IOException {
		// 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 生成一个样式  header 样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式   正常数据样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.WHITE.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        // 遍历集合数据，产生数据行
        Iterator it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            Object[] sourceCells = (Object[])it.next();
            for (int i = 0; i < sourceCells.length; i++) {
            	HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                Object obj =sourceCells[i];
                if(obj == null) {
                	cell.setCellValue(new HSSFRichTextString(""));
                } else if(obj instanceof Double) {
                	cell.setCellValue((Double)obj);
                } else if(obj instanceof Integer) {
                	cell.setCellValue(((Integer)obj).intValue());
                } else if(obj instanceof Date) {
                	//SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                	//cell.setCellValue(sdf.format((Date)obj));
                	cell.setCellValue((Date)obj);
                } else if(obj instanceof String){
                	cell.setCellValue(new HSSFRichTextString((String)obj));
                } else {
                	//cell.setCellValue(obj.toString());
                	cell.setCellValue(new HSSFRichTextString(obj.toString()));
                }
			}
        }
        workbook.write(os);
	}

	/**
	 * 获取单元格数据内容为字符串类型的数据
	 * @param cell Excel单元格
	 * @return String 单元格数据内容
	 */
	@SuppressWarnings("deprecation")
	private String getStringCellValue(HSSFCell cell) {
		String strCell = "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			strCell = cell.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			strCell = String.valueOf(cell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			strCell = "";
			break;
		default:
			strCell = "";
			break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		if (cell == null) {
			return "";
		}
		return strCell;
	}


	/**
	*根据HSSFCell类型设置数据 
	*@param cell Excel单元格    
	* @return String 单元格数据内容    
	*/  
	private String getCellFormatValue(HSSFCell cell) {
		String cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			// 如果当前Cell的Type为NUMERIC
			case HSSFCell.CELL_TYPE_NUMERIC:
				cellvalue = String.valueOf(cell.getNumericCellValue());
				break;
			case HSSFCell.CELL_TYPE_FORMULA: {
				// 判断当前的cell是否为Date
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，转化为Data格式
					// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
					// cellvalue = cell.getDateCellValue().toLocaleString();
					// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					cellvalue = sdf.format(date);
				} 
				break;
			} case HSSFCell.CELL_TYPE_STRING: // 如果当前Cell的Type为STRIN
				// 取得当前的Cell字符串
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			default:// 默认的Cell值
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}
	
	/***
	 * 首字母大写
	 * @param str
	 * @return
	 */
	private String upperCaseFirst(String str) {
		str =str.replaceFirst(str.substring(0, 1),str.substring(0, 1).toUpperCase()) ;
		return str;
	}
	
}
